<?
class Data_Player extends Data_Base {
    
    static $table = 'player';
    
    public function __construct(){
		parent::__construct ();
		parent::$table = self::$table;
    }
    //按条件获取
    function getPlayerListCount($filter){
        if($filter){
            $where = "WHERE ";
            foreach ($filter as $k=>$v){
                $where .= sprintf(" `%s`='%s' AND",$k,$v);
            }
            $where = trim($where,'AND');
        }
        $select = "count(1) as num";
        $sql =sprintf("SELECT %s FROM `player` %s",$select,$where);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //按条件获取
    function getPlayerList($filter,$order,$page,$limit){
        if($filter){
            $where = "WHERE ";
            foreach ($filter as $k=>$v){
                $where .= sprintf(" `%s`='%s' AND",$k,$v);
            }
            $where = trim($where,'AND');
        }
        $select = "id,name,teamid,position,number,height,weight,age,heavy_foot,heading,strength,speed,strong,energy,agility,dribbling,finishing,first_touch,short_pass,long_pass,marking,tackling,keep_goal,spirit,positioning,off_the_ball,decisions,flair,teamwork,skill1,salary,injured,injured_text,suspended,skill2,captain_skill,tired,energy_de,morale";
        $sql =sprintf("SELECT %s FROM `player` %s ORDER BY %s DESC LIMIT %d,%d",$select,$where,$order,($page-1)*$limit,$limit);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据
    function getPlayer($reg_number){
        $sql = sprintf("SELECT poto,team_id,replacement,reg_number,player_id,name,age,number,position_id,birth_date,height,weight,nationality,grade_id,first,lastr,lasty,birth_place,name_first_letter FROM `player_base` WHERE reg_number = '%s' LIMIT 1",$reg_number);
        $datas = $this->get_sql($sql);
        //        print_z($datas);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerIDGroupByYear($reg_number){
        //        if(empty($reg_number)) return false;
        $sql = sprintf("SELECT season,team_id,player_id,name FROM `player` WHERE reg_number = '%s' ",$reg_number);
        $datas = $this->get_sql($sql);
        //        print_z($datas);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerAllStats($player_id){
        if($player_id){
            foreach ($player_id as $per){
                $where .= sprintf(" (team_id = %02d AND player_id = %s AND season = '%s') OR",$per['team_id'],$per['player_id'],$per['season']);
            }
            $where = " WHERE ".trim($where,'OR');
            $sql = sprintf("SELECT '%s' as name,season,team_id,team_name,player_id,sum(if(enter='',0,1)) as appear,sum(if(main='首发',1,0)) as appear_start,sum(duration) as duration,sum(if(1st_yellow='',0,1)+if(2nd_yellow='',0,1)) as yellow,sum(if(red='',0,1)) as red FROM `game_player` %s GROUP BY season,player_id,team_id ",$per['name'],$where);
            $datas = $this->get_sql($sql);
        }
        //        print_z($datas);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerEveryStats($filter){
        if($filter){
            $where .= sprintf("WHERE g.player_id = '%s' AND g.team_id*1 = '%d' AND s.`round`*1 = '%d' AND g.season = '%s' AND s.season = '%s' AND (s.h_team_id*1 = '%d' OR s.v_team_id*1 = '%d') AND s.game_id = g.game_id",$filter['player_id'],$filter['team_id'],$filter['round'],$filter['season'],$filter['season'],$filter['team_id'],$filter['team_id']);
            $sql = sprintf("SELECT '%s' as name,g.season,g.team_id,g.team_name,g.player_id,if(enter='',0,1) as appear,if(main='首发',1,0) as appear_start,duration,if(1st_yellow='',0,1)+if(2nd_yellow='',0,1) as yellow,if(red='',0,1) as red,g.game_id as game_id,g.team as h_or_v,if(s.h_team_id*1=g.team_id,v_team_id*1,h_team_id*1) as against_team_id,s.date as date FROM `game_player` as g,schedule as s %s ",$filter['player_name'],$where);
            $datas = $this->get_sql($sql);
            //            print_r($datas['result']);
        }
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerEveryAssistStats($filter){
        $where .= sprintf("WHERE g.assis_player_id = '%s' AND g.team_id*1 = '%d' AND s.`round`*1 = '%d' AND g.season = '%s' AND s.season = '%s' AND (s.h_team_id*1 = '%d' OR s.v_team_id*1 = '%d') AND s.game_id = g.game_id",$filter['player_id'],$filter['team_id'],$filter['round'],$filter['season'],$filter['season'],$filter['team_id'],$filter['team_id']);
        $sql = sprintf("SELECT sum(if(s.h_team_id*1=g.team_id*1,1,0)) as h_assist,sum(if(s.v_team_id*1=g.team_id*1,1,0)) as v_assist,g.season,g.team_id,g.assis_player_id,count(assis_player_id) as assist FROM `game_goal` as g,`schedule` as s %s GROUP BY g.season,g.assis_player_id,g.team_id ",$where);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerEveryGoalsStats($filter){
        $where .= sprintf("WHERE g.player_id = '%s' AND g.team_id*1 = '%d' AND s.`round`*1 = '%d' AND g.season = '%s' AND s.season = '%s' AND (s.h_team_id*1 = '%d' OR s.v_team_id*1 = '%d') AND s.game_id = g.game_id",$filter['player_id'],$filter['team_id'],$filter['round'],$filter['season'],$filter['season'],$filter['team_id'],$filter['team_id']);
        $sql = sprintf("SELECT sum(if(s.h_team_id*1=g.team_id*1,1,0)) as h_goal,sum(if(s.v_team_id*1=g.team_id*1,1,0)) as v_goal,g.season,g.team_id,g.player_id,sum(if(g.method='本方乌龙球',0,1)) as goal,sum(if(g.method='本方乌龙球',1,0)) as own_goals,sum(if(g.method='点球'||g.method='点球射门'||g.method='球点球',1,0)) as penalty_kick,sum(if((g.method='直接任意球'||method='任意球'||method='任意球射'||method='任意球射门'||method='任意球直射')&&assis_player_id='',1,0)) as free_kick FROM `game_goal` as g,`schedule` as s %s GROUP BY season,player_id,team_id ",$where);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerAssistStats($player_id){
        foreach ($player_id as $per){
            $where .= sprintf(" (g.team_id = %02d AND g.assis_player_id = %s AND g.season = '%s') OR",$per['team_id'],$per['player_id'],$per['season']);
        }
        $where = " WHERE (".trim($where,'OR').")";
        $sql = sprintf("SELECT sum(if(s.h_team_id*1=g.team_id*1,1,0)) as h_assist,sum(if(s.v_team_id*1=g.team_id*1,1,0)) as v_assist,g.season,g.team_id,g.assis_player_id,count(assis_player_id) as assist FROM `game_goal` as g,`schedule` as s %s  AND s.game_id=g.game_id AND g.season=s.season GROUP BY g.season,g.assis_player_id,g.team_id ",$where);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerGoalsStats($player_id){
        foreach ($player_id as $per){
            $where .= sprintf(" (g.team_id = %02d AND g.player_id = %s AND g.season = '%s') OR",$per['team_id'],$per['player_id'],$per['season']);
        }
        $where = " WHERE (".trim($where,'OR').")";
        $sql = sprintf("SELECT sum(if(s.h_team_id*1=g.team_id*1,1,0)) as h_goal,sum(if(s.v_team_id*1=g.team_id*1,1,0)) as v_goal,g.season,g.team_id,g.player_id,sum(if(g.method='本方乌龙球',0,1)) as goal,sum(if(g.method='本方乌龙球',1,0)) as own_goals,sum(if(g.method='点球'||g.method='点球射门'||g.method='球点球',1,0)) as penalty_kick,sum(if((g.method='直接任意球'||method='任意球'||method='任意球射'||method='任意球射门'||method='任意球直射')&&assis_player_id='',1,0)) as free_kick FROM `game_goal` as g,`schedule` as s %s AND s.game_id=g.game_id AND g.season=s.season GROUP BY season,player_id,team_id ",$where);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerRank($season,$type = 'goal'){
        switch ($type){
            case 'goal':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,goal FROM `player_details` WHERE season = %s AND goal>0 ORDER BY goal DESC,`penalty_kick` ASC,`assists` DESC,`duration`ASC",$season);break;
            case 'assist':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,assists FROM `player_details` WHERE season = %s AND assists>0 ORDER BY `assists` DESC,goal DESC,`duration`ASC",$season);break;
            default:break;
        }
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getPlayerRankRound($season,$round,$type = 'goal'){
        switch ($type){
            case 'goal':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,goal FROM `player_details_round` WHERE season = %s AND round = %d AND goal>0 ORDER BY goal DESC,`penalty_kick` ASC,`assists` DESC,`duration`ASC",$season,$round);break;
            case 'assist':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,assists FROM `player_details_round` WHERE season = %s AND round = %d AND assists>0 ORDER BY `assists` DESC,goal DESC,`duration`ASC",$season,$round);break;
            default:break;
        }
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function getRankByTeam($season,$team_id,$type = 'goal',$limit=10,$offset=0){
        switch ($type){
            case 'goal':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,goal,h_goal,v_goal FROM `player_details` WHERE season = %s AND team_id = %d ORDER BY goal*1 DESC LIMIT %d,%d",$season,$team_id,$offset,$limit);break;
            case 'assist':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,assists,h_assist,v_assist FROM `player_details` WHERE season = %s AND team_id = %d ORDER BY `assists`*1 DESC LIMIT %d,%d",$season,$team_id,$offset,$limit);break;
            case 'duration':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,duration,assists,goal,appear,appear_start FROM `player_details` WHERE season = %s AND team_id = %d ORDER BY `duration`*1 DESC LIMIT %d,%d",$season,$team_id,$offset,$limit);;break;
            case 'card':$sql = sprintf("SELECT season,player_name,player_id,team_id,reg_number,(yellow+red) as card,red,yellow FROM `player_details` WHERE season = %s AND team_id = %d ORDER BY `card` DESC LIMIT %d,%d",$season,$team_id,$offset,$limit);break;
            default:break;
        }
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取某个球员数据技术统计
    function updateRankRound($filter,$rank,$rank_type = 'goal'){
        foreach ($filter as $key=>$per){
            $where .= sprintf(" `%s`='%s' AND",$key,$per);
        }
        switch ($rank_type){
            case 'goal':$type = 'goal_rank';break;
            case 'assist':$type = 'assist_rank';break;
            default:break;
        }
        $where = trim($where,'AND');
        $sql = sprintf("UPDATE `player_details_round` SET `%s`=%s WHERE %s",$type,$rank,$where);
        $datas = $this->insert_sql($sql);
        if($datas['errorcode']) exit($sql."\r\n this sql error!!!!\r\n");
        return $datas;
    }
    //获取某个球员数据技术统计
    function updateRank($filter,$rank,$rank_type = 'goal'){
        foreach ($filter as $key=>$per){
            $where .= sprintf(" `%s`='%s' AND",$key,$per);
        }
        switch ($rank_type){
            case 'goal':$type = 'goal_rank';break;
            case 'assist':$type = 'assist_rank';break;
            default:break;
        }
        $where = trim($where,'AND');
        $sql = sprintf("UPDATE `player_details` SET `%s`=%s WHERE %s",$type,$rank,$where);
        $datas = $this->insert_sql($sql);
        if($datas['errorcode']) exit($sql."\r\n this sql error!!!!\r\n");
        return $datas;
    }
    //更新player_detail信息
    function replacePlayerDetail($data){
        foreach ($data as $key=>$per){
            $set .= sprintf(" `%s`='%s' ,",$key,$per);
        }
        $set = trim($set,',');
        $sql = sprintf("REPLACE INTO `player_details` SET %s",$set);
        $datas = $this->insert_sql($sql);
        if($datas['errorcode']) exit($sql."\r\n this sql error!!!!\r\n");
        return $datas;
    }
    //更新player_detail信息
    function replacePlayerDetailRound($data){
        foreach ($data as $key=>$per){
            $set .= sprintf(" `%s`='%s' ,",$key,$per);
        }
        $set = trim($set,',');
        $sql = sprintf("REPLACE INTO `player_details_round` SET %s",$set);
        $datas = $this->insert_sql($sql);
        if($datas['errorcode']) exit($sql."\r\n this sql error!!!!\r\n");
        return $datas;
    }
    //更新game_player_stats信息
    function replacePlayerDetailStats($data){
        foreach ($data as $key=>$per){
            $set .= sprintf(" `%s`='%s' ,",$key,$per);
        }
        $set = trim($set,',');
        $sql = sprintf("REPLACE INTO `game_player_stats` SET %s",$set);
        $datas = $this->insert_sql($sql);
        if($datas['errorcode']) exit($sql."\r\n this sql error!!!!\r\n");
        return $datas;
    }
    //获取某个球队的所有球员
    function getPlayerStatById($id_list,$season){
        if($id_list!=null)
        foreach ($id_list as $per){
            $where .= sprintf("'%s',",$per);
        }
        $where = trim($where,',');
        $where = sprintf("reg_number in (%s)",$where);
        $sql = sprintf("SELECT * FROM `player_stats` WHERE %s AND season = '%s'",$where,$season);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取排名
    function getRank($season,$round,$orderby = 'goal',$limit=20,$offset=0){
        switch ($orderby){
            case 'goal':$orderby = 'goal_rank ASC';$where = 'AND goal_rank <>0';break;
            case 'assist':$orderby = 'assist_rank ASC';$where = 'AND assist_rank <>0';break;
            default:return false;break;
        }
        $sql = sprintf("SELECT * FROM `player_details_round` WHERE season='%s' AND round='%s' %s ORDER BY %s LIMIT %d,%d",$season,$round,$where,$orderby,$offset,$limit);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取球员赛季数据
    function getPlayerSchedule($reg_number,$season = null,$orderby = 'date desc',$limit = 20,$offset = 0){
        $season != null && $where = " season = '$season' AND";
        $where .=  " `reg_number`='$reg_number' AND";
        $where = trim($where,"AND");
        $orderby != null && $orderby = "ORDER BY $orderby";
        $sql = sprintf("SELECT *,substr(date,5,2) as month,substr(date,8,2) as day FROM `game_player_stats` WHERE %s %s LIMIT %d,%d",$where,$orderby,$offset,$limit);
        $datas = $this->get_sql($sql);
        return $datas;
    }
    //获取球员赛季数据
    function getPlayerDetail($reg_number,$season = null,$orderby = 'season asc'){
        $season != null && $where = " season = '$season' AND";
        $where .=  " `reg_number`='$reg_number' AND";
        $where = trim($where,"AND");
        $orderby != null && $orderby = "ORDER BY $orderby";
        $sql = sprintf("SELECT * FROM `player_details` WHERE %s %s",$where,$orderby);
        $datas = $this->get_sql($sql);
        return $datas;
    }
}
?>